package unipi.database;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

import unipi.helpers.Hash;
import unipi.helpers.ContactTypes;

public class User
{	
	public static ErrorCodes Add(String name, String surname, String email, String username, String password, String departmentID)
	{
		String usernames = "SELECT username FROM users WHERE username='"+username+"'";
		List<List<String>> users = SQLMethods.Select(usernames, 1);
		
		if(users == null) return ErrorCodes.databaseError;
		
		if (users.size() == 0)
		{
			password = Hash.SHA1(password);
			
			String columns = "name, surname, username, password, dptID";
			String values = "'" + name + "','" + surname + "','" + username + "','" + password + "'," + departmentID;			
			
			if (SQLMethods.Insert("users", columns, values) != -1)
			{
				String UserID = "SELECT id FROM users WHERE username='"+username + "'";
				List<List<String>> ids = SQLMethods.Select(UserID, 1);
				
				if(ids == null) return ErrorCodes.databaseError;
				if(ids.size() == 0)	return ErrorCodes.databaseError;
				
				return AddContactInfo(ids.get(0).get(0), email, ContactTypes.MainMail, "Registration email");
			}
		}
		
		return ErrorCodes.duplicateUsernames;
	}
	
	public static ErrorCodes AddContactInfo(String uid, String contact, ContactTypes type, String label)
	{
		String columns = "userID, contact, type, label";
		
		String values =  "'" + uid+ "','" + contact + "','" + type + "','" + label + "'";
		
		return SQLMethods.Insert("contactInfo", columns, values) != -1?ErrorCodes.OK: ErrorCodes.databaseError;
	}
	
	public static ErrorCodes deleteContact(String uid, String cid)
	{		
		return SQLMethods.Delete("ContactInfo", "userID = " + uid + " and id = " + cid + " and type != '" + ContactTypes.MainMail + "'")
													!= -1?ErrorCodes.OK: ErrorCodes.databaseError;	
	}
	
	public static ErrorCodes approveUsers(String[] userIDs)
	{
		String condition = "";
		
		condition += "id = " + userIDs[0];
		for(int x = 1; x < userIDs.length; x++)
		{
			condition += " or id = " + userIDs[x];			
		}
		
		return SQLMethods.Update("Users", "approved", "true", condition) != -1?ErrorCodes.OK: ErrorCodes.databaseError;		
	}
	
	public static String authUser(String username, String password)
	{
		password = Hash.SHA1(password);
		
		String query = "select username, password, id from ActiveUsers where username = '" +
						username + "' and password = '" + password + "'";
		
		List<List<String>> L = SQLMethods.Select(query, 3);
		
		if(L == null || L.size() == 0)
			return null;			
		
		List<String> cols = L.get(0);
		
		if(username.equals(cols.get(0)) && password.equals(cols.get(1)))
			return cols.get(2);
		else		
			return null;
		
	}
	
	public static boolean authUserCookie(String uid, String passhash)
	{
		String query = "select id, password from ActiveUsers where id = '" +
				uid + "' and password = '" + passhash + "'";
		
		List<List<String>> L = SQLMethods.Select(query, 2);
		
		if(L == null || L.size() == 0)
			return false;
		
		List<String> cols = L.get(0);
		
		if(uid.equals(cols.get(0)) && passhash.equals(cols.get(1)))
			return true;
		else
			return false;
		
	}
	
	public static boolean exists(String id)
	{
		int ncols = 1;
		String query = "select id from ActiveUsers where id = " + id;
		
		List<List<String>> user = SQLMethods.Select(query, ncols);
		
		if(user == null) return false;
		if(user.size() == 0) return false;
		
		else return true;		
	}
	
	public static List<Map<String, String>> getUserContactData(String id)
	{
		int ncols = 4;
		String query = "select id, `label`, contact, type " +
						"from ContactInfo " +
						"where userID = " + id;
		
		List<List<String>> contacts = SQLMethods.Select(query, ncols);
		
		if(contacts == null) return null;
		if(contacts.size() == 0) return null;
		
		List<Map<String, String>> contactList = new ArrayList<Map<String, String>>();
		
		for(List<String> contact : contacts)
		{
			Map<String, String> map = new HashMap<String, String>();
			map.put("id",		contact.get(0));
			map.put("label",	contact.get(1));
			map.put("contact",	contact.get(2));
			map.put("type",		contact.get(3));
			contactList.add(map);			
		}		

		return contactList;	
	}
	
	public static Map<String, String> getUserData(String id)
	{
		int ncols = 9;
		String query = "select u.name, u.surname, u.username, u.password, u.position, u.photo, u.hoursPerWeek, d.id, d.name " +
							"from ActiveUsers as u " +
							"left join departments as d " +
							"on u.dptID = d.id " +
							"where u.id = " + id;
		
		List<List<String>> result = SQLMethods.Select(query, ncols);
		
		if(result == null) return null;
		if(result.size() == 0) return null;
		
		List<String> userDataList = result.get(0);
		
		if(userDataList == null) return null;
		if(userDataList.size() != ncols) return null;
		
		Map<String, String> userData = new HashMap<String, String>();
		
		userData.put("name",			userDataList.get(0));
		userData.put("surname",			userDataList.get(1));
		userData.put("username",		userDataList.get(2));
		userData.put("password",		userDataList.get(3));
		userData.put("position",	 	userDataList.get(4));
		userData.put("photo",		 	userDataList.get(5));
		userData.put("hours",	 		userDataList.get(6));
		userData.put("dptID",			userDataList.get(7));
		userData.put("department",		userDataList.get(8));

			
		return userData;
	}
	
	public static List<List<String>> getUserRequestData(String id)
	{
		int ncols = 6;
		
		String query = "select t2.id, t2.name, t2.surname, c.contact, t2.username, d.name from Departments d " +
						"inner join " +
						"( " +
						"    select id, name, surname, position, username, u.dptID from Users u " +
						"    inner join " +
						"    ( " +
						"        select dptID from UserRightsPerDpt " +
						"        where userID = " + id + " and UserRights = true " +
						"    ) as t1 " +
						"    on t1.dptID = u.dptID " +
						"    where u.approved = false " +
						") as t2 " +
						"on d.id = t2.dptID " +
						"left join ContactInfo c " +
						"on t2.id = c.userID and c.type = '" + ContactTypes.MainMail + "';";
		
		return  SQLMethods.Select(query, ncols);		
	}
	
	public static ErrorCodes Edit(String id, String[] columns, String[] values)
	{
		return SQLMethods.Update("Users", columns, values, "id = " + id) != -1?ErrorCodes.OK: ErrorCodes.databaseError;
	}
	
	
	public static ErrorCodes Delete(String id)
	{		
		return SQLMethods.Update("Users", "deleted", "true", "id = " + id) != -1?ErrorCodes.OK: ErrorCodes.databaseError;	
	}
	
	public static ErrorCodes hardDelete(String id)
	{
		return SQLMethods.Delete("Users", "id = " + id) != -1?ErrorCodes.OK: ErrorCodes.databaseError;
	}
	
	public static ErrorCodes hardDelete(String[] ids)
	{
		String cond1 = "id = " + ids[0];
		String cond2 = "userID = " + ids[0];
		
		for(int x = 1; x < ids.length; x++)
		{
			cond1 += " or id = " + ids[x];
			cond2 += " or userID = " + ids[x];		
		}
		
		int contact = SQLMethods.Delete("ContactInfo", cond2);
		int user = SQLMethods.Delete("Users", cond1);
		
		return (contact*user >= 0) ?ErrorCodes.OK: ErrorCodes.databaseError;
		
	}
	
}
